﻿Imports System.Data.OleDb

Public Class ChiTietThoiKhoaBieuDAO
    Dim sDBName As String = "Database\thietkethoikhoabieu.mdb"

    Public Function LayDanhSachChiTietThoiKhoaBieu() As List(Of ChiTietThoiKhoaBieuDTO)
        Dim str = "SELECT * FROM ChiTietTKB"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of ChiTietThoiKhoaBieuDTO)

        While reader.Read()
            Dim cttkb As New ChiTietThoiKhoaBieuDTO
            cttkb.MaChiTietTKB = reader.GetString(0)
            cttkb.Tiet = reader.GetInt32(1)
            cttkb.MaGV = reader.GetString(2)
            cttkb.MaTKB = reader.GetString(3)
            cttkb.MaMonHoc = reader.GetString(4)
            ds.Add(cttkb)
        End While

        conn.Close()
        Return ds
    End Function

    Public Function ThemChiTietThoiKhoaBieu(ByVal cttkb As ChiTietThoiKhoaBieuDTO) As Integer
        Dim str = String.Format("INSERT INTO ChiTietTKB(MaChiTietTKB, Tiet, MaGV, MaTKB, MaMonHoc) " _
                & "values ('{0}','{1}','{2}','{3}','{4}')", cttkb.MaChiTietTKB, cttkb.Tiet, _
                cttkb.MaGV, cttkb.MaTKB, cttkb.MaMonHoc)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()
        conn.Close()

        Return ketqua
    End Function

    Public Function XoaChiTietThoiKhoaBieu(ByVal cttkb As ChiTietThoiKhoaBieuDTO) As Integer
        Dim str = "DELETE FROM ChiTietTKB WHERE MaChiTietTKB='" & cttkb.MaChiTietTKB & "'"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function SuaChiTietThoiKhoaBieu(ByVal cttkb As ChiTietThoiKhoaBieuDTO) As Integer
        Dim str = String.Format("UPDATE ChiTietTKB SET Tiet='{0}', MaGV='{1}', MaTKB='{2}', " _
                & "MaMonHoc='{3}' WHERE MaChiTietTKB='{4}'", cttkb.Tiet, cttkb.MaGV, cttkb.MaTKB, cttkb.MaMonHoc, cttkb.MaChiTietTKB)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function TraCuuChiTietThoiKhoaBieu(ByVal cttkb As ChiTietThoiKhoaBieuDTO) As List(Of ChiTietThoiKhoaBieuDTO)
        Dim sSelect = String.Format("SELECT * FROM ChiTietTKB WHERE MaChiTietTKB LIKE '%{0}%' AND Tiet LIKE '%{1}%' AND MaGV LIKE '%{2}%' " _
                    & "AND MaTKB LIKE '%{3}%' AND MaMonHoc LIKE '%{4}%' ", cttkb.MaChiTietTKB, cttkb.Tiet, cttkb.MaGV, cttkb.MaTKB, cttkb.MaMonHoc)

        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(sSelect, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of ChiTietThoiKhoaBieuDTO)

        While reader.Read()
            Dim rcttkb As New ChiTietThoiKhoaBieuDTO
            rcttkb.MaChiTietTKB = reader.GetString(0)
            rcttkb.Tiet = reader.GetString(1)
            rcttkb.MaGV = reader.GetString(2)
            rcttkb.MaTKB = reader.GetString(3)

            ds.Add(rcttkb)
        End While

        conn.Close()
        Return ds

    End Function

    Public Function LayTongSoTietCuaGV(ByVal cttkb As ChiTietThoiKhoaBieuDTO) As Integer
        Dim str = "SELECT COUNT (*) FROM ChiTietTKB WHERE MaGV = '" & cttkb.MaGV & "'"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()

        Dim ketqua = -1

        While reader.Read()
            ketqua = reader.GetInt32(0)
        End While

        conn.Close()
        Return ketqua
    End Function

    ' Lap Bao Cao Thoi Khoa Bieu
    Public Function LayBaoCaoThoiKhoaBieu() As Integer
        ' Xoa tat ca du lieu
        Dim str = "DELETE * FROM BaoCaoTKB"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmdDelete As New OleDbCommand(str, conn)
        cmdDelete.ExecuteNonQuery()

        ' Lay danh sach du thoi khoa bieu
        str = "SELECT tkb.TenLop, tkb.Thu, tkb.Buoi, ct.Tiet, mh.TenMonHoc, gv.TenGV " _
        & "FROM GiaoVien gv, ThoiKhoaBieu tkb, MonHoc mh, ChiTietTKB ct " _
        & "WHERE gv.MaGV = ct.MaGV AND tkb.MaTKB=ct.MaTKB AND mh.MaMonHoc=ct.MaMonHoc"

        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ketqua = -1

        While reader.Read()
            Dim bctkb As New BaoCaoThoiKhoaBieuDTO
            bctkb.TenLop = reader.GetString(0)
            bctkb.Thu = reader.GetString(1)
            bctkb.Buoi = reader.GetString(2)
            bctkb.Tiet = reader.GetInt32(3)
            bctkb.TenMonHoc = reader.GetString(4)
            bctkb.TenGV = reader.GetString(5)
            ' Luu thoi khoa bieu moi
            ketqua = LuuBaoCaoThoiKhoaBieu(bctkb)
        End While

        conn.Close()
        Return ketqua
    End Function

    Public Function LuuBaoCaoThoiKhoaBieu(ByVal bctkb As BaoCaoThoiKhoaBieuDTO) As Integer
        Dim str = String.Format("INSERT INTO BaoCaoTKB(TenLop, Thu, Buoi, Tiet, TenMonHoc, TenGV) " _
                & "values ('{0}','{1}','{2}','{3}','{4}','{5}')", bctkb.TenLop, bctkb.Thu, _
                bctkb.Buoi, bctkb.Tiet, bctkb.TenMonHoc, bctkb.TenGV)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()
        conn.Close()

        Return ketqua
    End Function

End Class
